<?php
/**
 * Upgrade from 1.0.5 to 1.1.0
 *
 * @author Steen Rabol <steen.rabol@gmail.com>
 * @link http://www.pradoportal.dk/
 * @copyright Copyright &copy; 2006,2007,2008 Steen Rabol
 * @license http://www.pradoportal.dk
 * @version $Id: Upgrade_1_0_5.php 354 2010-01-31 15:29:16Z steen.rabol@gmail.com $
 */

Prado::using('Application.Common.Upgrade.PortalUpgrade');
Prado::using('Application.Common.Data.PortalToDoRecord');
Prado::using('Application.Common.Data.PortalUserRecord');

class Upgrade_1_0_5 extends PortalUpgrade
{
	public function run()
	{
		parent::run();


		if(!$this->UpdateTables())
		{
			PortalUtil::fatalError("Upgrade faild: Update tables");
			return;
		}

		if(!$this->CreateNewTables())
		{

			PortalUtil::fatalError("Upgrade faild: Unable to create new tables");
			return;
		}

		if(!$this->DataUpgrade())
		{
			PortalUtil::fatalError("Upgrade faild: Data upgrade");
			return;
		}

		// This is too time consuming
		if(!$this->ImportLanguageStrings())
		{
			PortalUtil::fatalError("Upgrade faild: Import language");
			return;
		}

		// Create a todo for the admin
		$todo = new PortalToDoRecord();
		$todo->assignedby = 1;
		$todo->assignedto = 1;
		$todo->created		= time();
		$todo->createdby	= 1;
		$todo->title		= "Import langage files";
		$todo->description	= "Import language files - located in /Data/lang/";
		$todo->save();
		
		$this->UpdVersionNum('1.1.0');
	}

	public function UpdateTables()
	{
		$sql = array();
		$sql[] = "ALTER TABLE tblconfig ADD culture varchar(25) NOT NULL DEFAULT '' AFTER locked, MODIFY locked int(11) NOT NULL DEFAULT '0'";
		$sql[] = "ALTER TABLE tblpages DROP INDEX NAMEIDX;";
		$sql[] = "ALTER TABLE tblpages ADD path varchar(254) NULL DEFAULT NULL AFTER createdby, ADD fullpath varchar(254) NOT NULL DEFAULT '' AFTER path, ADD metakeywords text NOT NULL DEFAULT '' AFTER fullpath, ADD metadescription text NOT NULL DEFAULT '' AFTER metakeywords, ADD expire int(11) NOT NULL DEFAULT '0' AFTER metadescription";
		$sql[] = "ALTER TABLE tblpermissions MODIFY page varchar(254) NULL DEFAULT NULL";
		$sql[] = "ALTER TABLE tblportlets ADD hasadmin int(11) NOT NULL DEFAULT '0' AFTER active, ADD visible int(11) NOT NULL DEFAULT '1' AFTER hasadmin";
		$sql[] = "ALTER TABLE tblusers ADD culture varchar(25) NOT NULL DEFAULT 'en' AFTER sitenews";
		$sql[] = "ALTER TABLE tblhtmlportlet ADD css VARCHAR( 40 ) NOT NULL DEFAULT 'htmlportlet'";
		try
		{
			foreach($sql as $s)
			{
				$this->DbConnection->createCommand($s)->execute();
			}
		}
		catch(Exception $e)
		{
			PortalUtil::Log("Update tables:<br>" . $e->getMessage());
		}
		return true;
	}

	public function CreateNewTables()
	{
		$sql = array();
		$sql[] = "DROP TABLE IF EXISTS tbldownloadfiles";
		$sql[] = "CREATE TABLE tbldownloadfiles (
			id int(11) NOT NULL auto_increment,
			grpid int(11) NOT NULL DEFAULT '0',
			filename varchar(45) NOT NULL DEFAULT '',
			active int(10) unsigned NOT NULL DEFAULT '0',
			comment text NOT NULL,
			description varchar(45) NOT NULL DEFAULT '',
			dlcount int(10) unsigned NOT NULL DEFAULT '0',
			filesize int(10) unsigned NOT NULL DEFAULT '0',
			filedate int(10) unsigned NOT NULL DEFAULT '0',
			PRIMARY KEY (id))";

		$sql[] = "DROP TABLE IF EXISTS tbldownloadportletgrps";
		$sql[] = "CREATE TABLE tbldownloadportletgrps (
				id int(10) unsigned NOT NULL auto_increment,
				name varchar(45) NOT NULL DEFAULT '',
				description text NOT NULL,
				role int(10) unsigned NOT NULL DEFAULT '0',
				active int(10) unsigned NOT NULL DEFAULT '1',
				PRIMARY KEY (id)
			)";

		$sql[] = "DROP TABLE IF EXISTS tblhelp";
		$sql[] = "CREATE TABLE tblhelp (
				id int(11) NOT NULL auto_increment,
				pagename varchar(45) NOT NULL DEFAULT '',
				culture varchar(25) NOT NULL DEFAULT '',
				content text NOT NULL,
				title varchar(45) NOT NULL DEFAULT '',
				PRIMARY KEY (id)
			)";

		$sql[] = "DROP TABLE IF EXISTS tbljscookmenu";
		$sql[] = "CREATE TABLE tbljscookmenu (
				id int(11) NOT NULL DEFAULT 0,
				theme varchar(25) NOT NULL DEFAULT '',
				menuorientation varchar(3) NOT NULL DEFAULT 'hbr',
				needlogin int(11) NOT NULL DEFAULT '0',
				active int(11) NOT NULL DEFAULT '1',
				name varchar(40) NOT NULL DEFAULT '',
				sitemenuid int(11) NOT NULL DEFAULT '0',
				PRIMARY KEY (id)
			)";

		$sql[] = "DROP TABLE IF EXISTS tbljscookmenuitem";
		$sql[] = "CREATE TABLE tbljscookmenuitem (
				id int(11) NOT NULL  auto_increment,
				menuid int(11) NOT NULL DEFAULT 0,
				imagepath varchar(128) NULL DEFAULT NULL,
				title varchar(128) NOT NULL DEFAULT '',
				url varchar(254) NULL DEFAULT NULL,
				target varchar(10) NOT NULL DEFAULT '',
				isbreak int(11) NOT NULL DEFAULT 0,
				parentid int(11) NOT NULL DEFAULT '0',
				displayorder decimal(7,3) NOT NULL DEFAULT '0.000',
				needlogin int(11) NOT NULL DEFAULT '0',
				showafterlogin int(11) NOT NULL DEFAULT '1',
				PRIMARY KEY (id)
			)";

		$sql[] = "DROP TABLE IF EXISTS tbllangstrings";
		$sql[] = "CREATE TABLE tbllangstrings (
				id int(11) NOT NULL  auto_increment,
				culture varchar(25) NOT NULL DEFAULT '',
				lkey varchar(128) NOT NULL DEFAULT '',
				fromvalue text NOT NULL,
				tovalue text NOT NULL,
				PRIMARY KEY (id)
			)";

		$sql[] = "DROP TABLE IF EXISTS tblpagepath";
		$sql[] = "CREATE TABLE tblpagepath (
				id int(11) NOT NULL  auto_increment,
				path varchar(254) NOT NULL DEFAULT '',
				name varchar(65) NOT NULL DEFAULT '',
				active int(11) NOT NULL DEFAULT '1',
				role int(11) NOT NULL DEFAULT '0',
				PRIMARY KEY (id),
				UNIQUE name (name)
			)";

		$sql[] = "DROP TABLE IF EXISTS tblsimpletext";
		$sql[] = "CREATE TABLE tblsimpletext (
				id int(11) NOT NULL ,
				content text NOT NULL,
				css varchar(40) NOT NULL DEFAULT '',
				PRIMARY KEY (id)
			)";

		$sql[] = "DROP TABLE IF EXISTS tblsitemenu";
		$sql[] = "CREATE TABLE tblsitemenu (
				id int(11) NOT NULL  auto_increment,
				name varchar(45) NOT NULL DEFAULT '',
				active int(11) NOT NULL DEFAULT '1',
				role int(11) NOT NULL DEFAULT '0',
				PRIMARY KEY (id)
			)";

		$sql[] = "DROP TABLE IF EXISTS tblsitemenuitem";
		$sql[] = "CREATE TABLE tblsitemenuitem (
				id int(11) NOT NULL auto_increment,
				smid int(11) NOT NULL DEFAULT 0,
				title varchar(45) NOT NULL DEFAULT '',
				page varchar(128) NOT NULL DEFAULT '',
				img varbinary(128) NOT NULL DEFAULT '',
				active int(11) NOT NULL DEFAULT 0,
				role int(11) NOT NULL DEFAULT 0,
				parent int(11) NOT NULL DEFAULT '0',
				target varchar(10) NOT NULL DEFAULT '_self',
				isbreak int(11) NOT NULL DEFAULT '0',
				displayorder decimal(7,3) NOT NULL DEFAULT '999.999',
				needlogin int(11) NOT NULL DEFAULT '0',
				showafterlogin int(11) NOT NULL DEFAULT '1',
				PRIMARY KEY (id)
			)";

		$sql[] = "DROP TABLE IF EXISTS tbltheme";
		$sql[] = "CREATE TABLE tbltheme (
				id int(11) NOT NULL  auto_increment,
				name varchar(40) NOT NULL DEFAULT '',
				systemname varchar(40) NOT NULL DEFAULT '',
				active int(11) NOT NULL DEFAULT 0,
				PRIMARY KEY (id)
			)";

		$sql[] = "DROP TABLE IF EXISTS tblthemecssclasses";
		$sql[] = "CREATE TABLE tblthemecssclasses (
				id int(11) NOT NULL  auto_increment,
				themeid int(11) NOT NULL DEFAULT 0,
				name varchar(40) NOT NULL DEFAULT '',
				PRIMARY KEY (id)
			)";

		$sql[] = "DROP TABLE IF EXISTS tblthemecssclassesattrib";
		$sql[] = "CREATE TABLE tblthemecssclassesattrib (
				tcid int(11) NOT NULL DEFAULT 0,
				name varchar(40) NOT NULL DEFAULT '',
				attrib varchar(128) NOT NULL DEFAULT ''
			)";

		$sql[] = "DROP TABLE IF EXISTS tblthemetemplate";
		$sql[] = "CREATE TABLE tblthemetemplate (
				id int(11) NOT NULL  auto_increment,
				tid int(11) NOT NULL DEFAULT 0,
				name varchar(128) NOT NULL DEFAULT '',
				content text NOT NULL,
				PRIMARY KEY (id)
			);";

		$sql[] = "DROP TABLE IF EXISTS tbltodo";
		$sql[] = "CREATE TABLE tbltodo (
				id int(3) NOT NULL  auto_increment,
				title varchar(50) NULL DEFAULT '0',
				description text NULL DEFAULT NULL,
				created int(11) NOT NULL DEFAULT '0',
				deadline int(11) NOT NULL DEFAULT '0',
				priority int(11) NOT NULL DEFAULT '0',
				assignedto int(11) NOT NULL DEFAULT '0',
				createdby int(11) NOT NULL DEFAULT '0',
				status int(11) NOT NULL DEFAULT '0',
				assignedby int(11) NOT NULL DEFAULT 0,
				PRIMARY KEY (id)
			)";

		$sql[] = "DROP TABLE IF EXISTS tbltodohistory";
		$sql[] = "CREATE TABLE tbltodohistory (
				id int(11) NOT NULL  auto_increment,
				todoid int(11) NOT NULL DEFAULT '0',
				answer text NOT NULL,
				created int(11) NOT NULL DEFAULT '0',
				userid int(11) NOT NULL DEFAULT '0',
				PRIMARY KEY (id)
			)";

		$sql[] = "DROP TABLE IF EXISTS tbluseractivity";
		$sql[] = "CREATE TABLE tbluseractivity (
				id int(11) NOT NULL  auto_increment,
				userid int(11) NOT NULL DEFAULT 0,
				`timestamp` int(11) NOT NULL DEFAULT 0,
				ip varchar(28) NOT NULL DEFAULT '',
				act longblob NOT NULL,
				PRIMARY KEY (id)
			);";

		$sql[] = "DROP TABLE IF EXISTS tbluseronline";
		$sql[] = "CREATE TABLE tbluseronline (
				id int(11) NOT NULL auto_increment,
				ip varchar(32) NOT NULL DEFAULT '',
				`timestamp` int(11) NOT NULL DEFAULT 0,
				sessionid varchar(32) NOT NULL DEFAULT '',
				userid int(11) NOT NULL DEFAULT 0,
				PRIMARY KEY (id)
			)";

		try
		{
			foreach($sql as $s)
			{
				$this->DbConnection->createCommand($s)->execute();
			}
		}
		catch(Exception $e)
		{
			PortalUtil::fatalError("Create tables:<br>" . $e->getMessage());
			return false;
		}

		return true;
	}

	public function ImportLanguageStrings()
	{
		// Clear the table
		$this->DbConnection->createCommand("delete from tbllangstrings")->execute();
		$filepath	= $this->Application->PortalBasePath . '/protected/Data/lang/';
		// load the XML files from the upgrade files
		$languagefiles	= $this->getLangFiles($filepath);
		if(count($languagefiles) > 0)
		{
			foreach($languagefiles as $lfile)
			{
				$strings = $this->loadlangfile($filepath.$lfile);
				if($strings)
				{
					$path_parts = pathinfo($lfile);
					$culture	= $path_parts['filename'];
					foreach($strings as $string)
					{
						if(PortalUtil::isUTF8($string[0]))
						{
							$fromvalue	= $this->DbConnection->quoteString($string[2]);
						}
						else
						{
							$fromvalue	= $this->DbConnection->quoteString(utf8_encode($string[2]));
						}

						if(PortalUtil::isUTF8($string[1]))
						{
							$tovalue	= $this->DbConnection->quoteString($string[3]);
						}
						else
						{
							$tovalue	= $this->DbConnection->quoteString(utf8_encode($string[3]));
						}
						try
						{
							$sql = "insert into tbllangstrings (culture,lkey,fromvalue,tovalue) values('$culture','APPLICATION',$fromvalue,$tovalue)";
							$cmd = $this->DbConnection->createCommand($sql)->execute();
						}
						catch(Exception $e)
						{
							PortalUtil::fatalError("Faild to import language: $culture - <br>" . $e->getMessage());
							return false;
						}
					}
				}
			}
		}

		return true;
	}

	public function getLangFiles($filepath)
	{
		$lfiles 		= array();
		if(!is_dir($filepath))
		{
			return false;
		}

		if(!file_exists($filepath))
		{
			return false;
		}

		$folder = opendir($filepath);
		while($file = readdir($folder))
		{
			if($file!=='.' && $file!=='..' && $file!=='.svn' && !is_dir($filepath.'/'.$file))
			{
				$path_parts = pathinfo($file);
				if($path_parts['extension'] == "xml")
				{
					if($file == "en.xml")
						$lfiles[] = $file;
				}
			}
		}
		closedir($folder);
		return $lfiles;
	}

	public function loadlangfile($filename)
	{
		$XML = simplexml_load_file($filename);

		if(!$XML)
		{
			return false;
		}

		$translationStrings = $XML->xpath('//string');
		$translations = array();

		foreach($translationStrings as $string)
		{
			$attr = $string->attributes();
			$translations[] = array((string)$attr->id,(string)$attr->lkey,(string)$string->fromvalue,(string)$string->tovalue);
		}
		return $translations;
	}

	public function DataUpgrade()
	{
		// Delete old portalhtmlarea
		$pafile	= $this->Application->PortalBasePath . '/protected/Common/PortalHtmlArea.php';
		@unlink($pafile);
		// Set da and en as active language
		try
		{
			$this->DbConnection->createCommand("update tbllanguages set active=1 where culture='en'")->execute();
		}
		catch(Exception $e)
		{
			PortalUtil::fatalError("Data upgrade:<br>" . $e->getMessage());
			return false;
		}

		try
		{
			$this->DbConnection->createCommand("update tbllanguages set active=1 where culture='da'")->execute();
		}
		catch(Exception $e)
		{
			PortalUtil::fatalError("Data upgrade:<br>" . $e->getMessage());
			return false;
		}

		// SimpleText portlet moved from config table to it's own table
		try
		{
			$stRecords = $this->DbConnection->createCommand("select * from tblconfig where name like 'st_%'")->query();
			if($stRecords)
			{
				foreach($stRecords as $stRecord)
				{
					$id			= substr($stRecord['name'],3,999);
					$content	= $stRecord['value'];
					$css		= 'simpletextportlet';

					$this->DbConnection->createCommand("insert into tblsimpletext (id,content,css) values('$id','$content','$css')")->execute();

					// Delete the old record
					$this->DbConnection->createCommand("delete from tblconfig where id=" . $stRecord['id'])->execute();
				}
			}
		}
		catch(Exception $e)
		{
			PortalUtil::fatalError("Data upgrade:<br>" . $e->getMessage());
		}

		// Update full path
		try
		{
			$this->DbConnection->createCommand("UPDATE tblpages SET FULLPATH=NAME WHERE FULLPATH=''")->execute();
		}
		catch(Exception $e)
		{
			PortalUtil::fatalError("Data upgrade:<br>" . $e->getMessage());
		}

		// Update full path
		try
		{
			$this->DbConnection->createCommand("UPDATE tblportlets SET visible=0 where name='AdminPortlet'")->execute();
		}
		catch(Exception $e)
		{
			PortalUtil::fatalError("Data upgrade:<br>" . $e->getMessage());
		}

		// Add constrain on the table
		try
		{
			$this->DbConnection->createCommand("ALTER TABLE tblpages ADD UNIQUE fullpath (fullpath)")->execute();
		}
		catch(Exception $e)
		{
			PortalUtil::fatalError("Data upgrade:<br>" . $e->getMessage());
		}

		return true;
	}
}
?>